/**
 * TakenProjectBriefDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.TakenProjectBriefDTO;
import pits.util.Constants;
import pits.util.Logger;

public class TakenProjectBriefDAO {

	public List<TakenProjectBriefDTO> getData(String employeeID) {

		List<TakenProjectBriefDTO> result = new ArrayList<TakenProjectBriefDTO>();
		TakenProjectBriefDTO runnerDTO = null;

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;

		ResultSet projectexpResultSet = null;
		ResultSet projectResultSet = null;
		ResultSet departResultSet = null;
		ResultSet langResultSet = null;
		ResultSet DBResultSet = null;
		ResultSet frameResultSet = null;
		ResultSet techResultSet = null;
		ResultSet roleResultSet = null;

		String languages = null;
		String DBMS = null;
		String frameTech = null;
		String roles = null;

		try {
			query = "SELECT * FROM projectexp WHERE EmpID=" + employeeID + " AND isDeleted=0";
			// new Logger().writeln(query);

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);
			projectexpResultSet = sqlStatement.executeQuery();

			while (projectexpResultSet.next()) {

				query = "SELECT * FROM project WHERE ProjectID=" + projectexpResultSet.getInt("ProjectID")
				        + " AND isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				projectResultSet = sqlStatement.executeQuery();

				if (projectResultSet.next()) {

					runnerDTO = new TakenProjectBriefDTO();

					query = "SELECT * FROM department WHERE DepartID=" + projectResultSet.getInt("DepartID")
					        + " AND isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					departResultSet = sqlStatement.executeQuery();
					departResultSet.next();

					query = "SELECT * FROM language l, project_language pl" + " WHERE pl.ProjectID="
					        + projectResultSet.getInt("ProjectID") + " AND l.LangID=pl.LangID" + " AND l.isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					langResultSet = sqlStatement.executeQuery();
					languages = "-";
					while (langResultSet.next()) {
						languages += ", " + langResultSet.getString("Name");
						// new Logger().write(languages);
					}
					if (!languages.equals("-")) {
						languages = languages.substring(2);
					}

					// new Logger().write(languages);

					query = "SELECT * FROM pits_db.database d, project_database pd" + " WHERE pd.ProjectID="
					        + projectResultSet.getInt("ProjectID") + " AND d.DatabaseID=pd.DatabaseID"
					        + " AND d.isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					DBResultSet = sqlStatement.executeQuery();
					DBMS = "-";
					while (DBResultSet.next()) {
						DBMS += ", " + DBResultSet.getString("Name");
					}
					if (!DBMS.equals("-")) {
						DBMS = DBMS.substring(2);
					}

					query = "SELECT * FROM framework f, project_framework pf" + " WHERE pf.ProjectID="
					        + projectResultSet.getInt("ProjectID") + " AND f.FrameID=pf.FrameID" + " AND f.isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					frameResultSet = sqlStatement.executeQuery();
					frameTech = "-";
					while (frameResultSet.next()) {
						frameTech += ", " + frameResultSet.getString("Name");
					}

					query = "SELECT * FROM technology t, project_technology pt" + " WHERE pt.ProjectID="
					        + projectResultSet.getInt("ProjectID") + " AND t.TechID=pt.TechID" + " AND t.isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					techResultSet = sqlStatement.executeQuery();
					while (techResultSet.next()) {
						frameTech += ", " + techResultSet.getString("Name");
					}
					if (!frameTech.equals("-")) {
						frameTech = frameTech.substring(2);
					}

					query = "SELECT * FROM role r, projectexp e" + " WHERE r.RoleID="
					        + projectexpResultSet.getInt("RoleID") + " AND e.ProjectID="
					        + projectexpResultSet.getInt("ProjectID") + " AND e.EmpID=" + employeeID
					        + " AND r.isDeleted=0";
					// new Logger().writeln(query);
					sqlStatement = conn.prepareStatement(query);
					roleResultSet = sqlStatement.executeQuery();
					roles = "-";
					while (roleResultSet.next()) {
						roles += ", " + roleResultSet.getString("Role");
					}
					if (!roles.equals("-")) {
						roles = roles.substring(2);
					}

					runnerDTO.setName(projectResultSet.getString("ProjectName"));
					runnerDTO.setDepartName(departResultSet.getString("DepartName"));
					runnerDTO.setStartDate(projectResultSet.getString("PeriodFrom"));
					runnerDTO.setEndDate(projectResultSet.getString("PeriodTo"));
					runnerDTO.setRole(roles);
					runnerDTO.setDescription(projectResultSet.getString("Description"));
					runnerDTO.setLanguages(languages);
					runnerDTO.setFrameTech(frameTech);
					runnerDTO.setDBMS(DBMS);
					runnerDTO.setProjectID(projectResultSet.getInt("ProjectID"));

					result.add(runnerDTO);
				}
			}
		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				// projectexpResultSet.close();
				// projectResultSet.close();
				// departResultSet.close();
				// langResultSet.close();
				// DBResultSet.close();
				// frameResultSet.close();
				// techResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		return result;
	}
}
